﻿using System.Collections.Generic;
using System.Threading.Tasks;
using System;
using VolPro.Core.Controllers.Basic;
using VolPro.Core.DbSqlSugar;
using VolPro.Sys.IRepositories;
using Microsoft.AspNetCore.Mvc;
using VolPro.Core.Services;
using VolPro.Core.Filters;
using VolPro.Core.Enums;
using VolPro.Entity.DomainModels;

namespace VolPro.WebApi.Controllers.DataView
{
    [Route("api/dataViewDb")]
    public class DataViewDbController : VolController
    {
        private IDataViewSqlConfigRepository _repository { get; set; }
        public DataViewDbController(IDataViewSqlConfigRepository repository)
        {
            _repository = repository;
        }


        [ActionPermission(ActionPermissionOptions.Add| ActionPermissionOptions.Update)]
        [Route("execSql"),HttpPost]
        public async Task<object> Exec([FromBody] DataViewSqlConfig data)
        {
            return await Exec(null,data:data,test:true);
        }

        /// <summary>
        /// 编译执行sql
        /// </summary>
        /// <param name="id"></param>
        /// <param name="view"></param>
        /// <returns></returns>
        [Route("getDbData"), HttpPost]
       public async Task<object> GetDbData(string code)
       {
           if (string.IsNullOrEmpty(code)) return Error("未获取到编号");

           return await Exec(code);
       }
        private async Task<object> Exec(string? code =null, DataViewSqlConfig? data=null,bool test=false)
        {
            if (data==null)
            {
                data = _repository.FindAsIQueryable(x => x.Code == code).FirstOrDefault();
            }
            if (string.IsNullOrEmpty(data.DataSql)) return Error($"未配置编号[{code}]的sql");
            try
            {
                string sql = FilterSql(data.DataSql);
                var ado = DbManger.GetConnection(data.DbService).Ado;
                if (data.IsProc == 1)
                {
                    ado = ado.UseStoredProcedure();
                }
                var result = await ado.SqlQueryAsync<object>(sql);
                return result;
            }
            catch (Exception ex)
            {
                string message = $"大屏sql执行{(data.IsProc == 1 ? "存储过程" : "")}异常,：{data.DataSql},{ex.Message + ex.InnerException + ex.StackTrace}";
                Logger.Error(message);
                if (test)
                {
                    return Error(message);
                }
                return Error($"大屏编码【{code}】执行{(data.IsProc == 1 ? "存储过程" : "sql")}异常");
            }
        }

        private static string[] illegalKeywords = { " delete ", " drop ", " truncate ", " update ", " insert ", " alter ", " create ", " grant ", " revoke ", " exec ", " execute ", " shutdown " };

        private string FilterSql(string sql)
        {
            foreach (string keyword in illegalKeywords)
            {
                sql = sql.Replace(keyword, "", StringComparison.OrdinalIgnoreCase);
            }
            return sql;
        }

    }
}
